package com.fatsatsuma.spreadsheets;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.io.InputStream;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.Iterator;
import java.util.List;
import java.util.Map;

import org.apache.commons.beanutils.BeanUtils;
import org.apache.log4j.Logger;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;

/**
 * @author $Id: SpreadsheetReader.java,v 1.1 2007/06/07 10:12:56 gevans Exp $
 */
public class SpreadsheetReader {

	private static Logger log = Logger.getLogger(SpreadsheetReader.class);

	private HSSFWorkbook workbook;

	private List<RowListener> rowListeners = new ArrayList<RowListener>();

	public void registerRowListener(RowListener rowListener) {
		rowListeners.add(rowListener);
	}

	public void unregisterRowListener(RowListener rowListener) {
		rowListeners.remove(rowListener);
	}

	public void clearRowListeners() {
		rowListeners.clear();
	}

	protected void notifyListenersWithRow(Map<String, Object> row) {
		for (RowListener rowListener : rowListeners) {
			try {
				BeanUtils.copyProperties(rowListener, row);
				rowListener.execute();
			} catch (Exception exp) {
				exp.printStackTrace();
			}
		}
	}

	public SpreadsheetReader(File spreadsheet) throws FileNotFoundException, InvalidWorkbookException {
		this(new FileInputStream(spreadsheet));
	}

	public SpreadsheetReader(InputStream spreadsheet) throws InvalidWorkbookException {
		try {
			workbook = new HSSFWorkbook(spreadsheet);
		} catch (IOException exp) {
			throw new InvalidWorkbookException("Invalid workbook", exp);
		}
	}

	public List<String> getHeaders(String sheetName) throws WorksheetDoesNotExistException {
		return getHeadersFromFile(workbook, sheetName);
	}

	public List<Map<String, Object>> getData(String sheetName) throws WorksheetDoesNotExistException {
		List<String> headers = getHeadersFromFile(workbook, sheetName);
		if (headers == null) {
			return null;
		}
		return getDataFromFile(workbook, sheetName, headers);
	}

	public static List<String> getHeadersFromFile(HSSFWorkbook workbook, String sheetName)
			throws WorksheetDoesNotExistException {
		HSSFSheet sheet = workbook.getSheet(sheetName);

		if (sheet == null) {
			throw new WorksheetDoesNotExistException(sheetName);
		}

		HSSFRow row = sheet.getRow(0);

		return readRow(row);
	}

	public static List<Map<String, Object>> getDataFromFile(HSSFWorkbook workbook, String sheetName,
			List<String> headers) throws WorksheetDoesNotExistException {
		HSSFSheet sheet = workbook.getSheet(sheetName);

		if (sheet == null) {
			throw new WorksheetDoesNotExistException(sheetName);
		}

		HSSFRow row = null;

		int count = 1;
		List<Map<String, Object>> data = new ArrayList<Map<String, Object>>();

		while ((row = sheet.getRow(count++)) != null) {
			if (log.isDebugEnabled()) {
				log.debug("reading Data from Row " + (count - 1));
			}

			Map<String, Object> map = readDataRow(row, headers);
			if (isMapEmpty(map)) {
				log.warn("Found an empty row '" + count + "'");
				break;
			}
			data.add(map);
		}

		return data;
	}

	public static boolean isMapEmpty(Map<String, Object> map) {
		Iterator<Map.Entry<String, Object>> iterator = map.entrySet().iterator();
		while (iterator.hasNext()) {
			Map.Entry<String, Object> entry = iterator.next();
			if ("sessionid".equals(entry.getKey())) {

			} else {
				if (entry.getValue() != null) {
					if (!"".equals(entry.getValue())) {
						return false;
					}
				}
			}
		}
		return true;
	}

	public static List<String> readRow(HSSFRow row) {
		HSSFCell cell = null;
		List<String> rowData = new ArrayList<String>();

		int size = row.getLastCellNum();

		if (log.isDebugEnabled()) {
			log.debug("Number of cells in row : " + size);
		}

		int start = 0; // row.getFirstCellNum();
		for (int i = start; i < size; i++) {
			cell = row.getCell((short) i);
			if (log.isDebugEnabled()) {
				log.debug("Reading cell : " + i);
			}

			if (cell == null) {
				return rowData;
			} else if (cell.getCellType() == HSSFCell.CELL_TYPE_STRING) {
				if (cell.getStringCellValue().equals("NULL")
						|| cell.getStringCellValue().trim().length() == 0) {
					// this is the end of the row, as we are only reading the headers we
					// can return
					return rowData;
				} else {
					rowData.add(cell.getStringCellValue());
					if (log.isDebugEnabled()) {
						log.debug("Reading header string " + cell.getStringCellValue());
					}
				}
			} else if (cell.getCellType() == HSSFCell.CELL_TYPE_BLANK) {
				return rowData;
			} else {
				throw new RuntimeException("Unable to read non-string header '" + cell.getCellType() + "'");
			}
		}
		return rowData;
	}

	public static Map<String, Object> readDataRow(HSSFRow row, List<String> headers) {
		HSSFCell cell = null;
		Map<String, Object> rowData = new HashMap<String, Object>();

		Iterator<String> iterator = headers.iterator();
		int count = 0;
		while (iterator.hasNext()) {
			cell = row.getCell((short) count++);
			String header = iterator.next();
			if (cell == null) {
				log.debug("Null found " + header);
				rowData.put(header, null);
			} else {
				switch (cell.getCellType()) {
				case HSSFCell.CELL_TYPE_STRING:

					if (cell.getStringCellValue().equals("NULL")) {
						log.debug("'NULL' found " + header);
						rowData.put(header, null);
					} else {
						// rowData.put( header , cell.getStringCellValue() ==
						// null ? cell.getStringCellValue() :
						// cell.getStringCellValue().trim() );

						String cellValue = cell.getStringCellValue() == null ? "" : cell.getStringCellValue()
								.trim();
						try {
							// 2003-07-24 00:00:00
							Date date = new SimpleDateFormat("yyyy-MM-dd hh:mm:ss").parse(cellValue);
							rowData.put(header, date);
						} catch (Exception exp) {
							rowData.put(header, cellValue);
						}

						if (log.isDebugEnabled()) {
							log.debug("Reading '" + header + "' as string " + cell.getStringCellValue());
						}
					}

					break;

				case HSSFCell.CELL_TYPE_NUMERIC:

					if (HSSFDateUtil.isCellDateFormatted(cell)) {
						Date date = cell.getDateCellValue();
						rowData.put(header, date);
						if (log.isDebugEnabled()) {
							log.debug("Reading '" + header + "' as date " + cell.getDateCellValue());
						}
					} else {
						// log.debug( "reading double " +
						// cell.getNumericCellValue() );
						Double d = new Double(cell.getNumericCellValue());
						if (new Double((double) d.intValue()).compareTo(d) == 0) {
							rowData.put(header, new Integer((int) cell.getNumericCellValue()));
							if (log.isDebugEnabled()) {
								log.debug("Reading '" + header + "' as integer " + cell.getNumericCellValue());
							}
						} else {
							rowData.put(header, new Double(cell.getNumericCellValue()));
							if (log.isDebugEnabled()) {
								log.debug("Reading '" + header + "' as double " + cell.getNumericCellValue());
							}
						}
					}

					break;

				case HSSFCell.CELL_TYPE_BOOLEAN:

					rowData.put(header, new Boolean(cell.getBooleanCellValue()).toString());
					if (log.isDebugEnabled()) {
						log.debug("Reading '" + header + "' as boolean " + cell.getBooleanCellValue());
					}

					break;

				case HSSFCell.CELL_TYPE_BLANK:
					rowData.put(header, "");

					break;

				default:
					throw new RuntimeException("Unknown Cell Type : " + cell.getCellType());
					// rowData.put( header , "ERROR" + cell.getCellType());
				}
			}
		}
		return rowData;
	}

	public static String getString(Object input) {
		if (input == null) {
			return null;
		} else if (input instanceof String) {
			return (String) input;
		} else {
			return String.valueOf(input);
		}
	}

	public static Double getDouble(Object input) {
		if (input == null) {
			return null;
		} else if (input instanceof Double) {
			return (Double) input;
		} else if (input instanceof Integer) {
			return ((Integer) input).doubleValue();
		} else if (input instanceof String) {
			return null;
		} else {
			return null;
		}
	}

	public static Integer getInteger(Object input) {
		if (input == null) {
			return null;
		} else if (input instanceof Double) {
			return ((Double) input).intValue();
		} else if (input instanceof Integer) {
			return (Integer) input;
		} else if (input instanceof String) {
			return null;
		} else {
			return null;
		}
	}

	public void readWorksheet(String worksheetName) throws WorksheetDoesNotExistException {
		List<Map<String, Object>> data = getData(worksheetName);
		for (Map<String, Object> row : data) {
			notifyListenersWithRow(row);
		}
	}
}
